<?php
/**
 * It runs for monthly tasks.
 * 
 *   1. Send report email for merchants
 *
 * LBB Solutions Oy 版权所有 © 2013
 */
class MonthlyCommand extends CConsoleCommand {
	
	public function run($args) {
		echo 'Running Monthly: ' . print_r($args, true);
		Yii::app()->setTimeZone('Europe/Helsinki');
		
		$this->createBizReportMonthly(date('Y-m-d'));
	}
	
	public function createBizReportMonthly($date) {
		//1. get all merchants with their own email
		$Merchants = $this->_fetchMerchants();
		
		//2. create reporting pictures for each merchant
		$Reports = $this->_fetchBizReport($Merchants, $date);
		
		//3. send email one by one
		//BUtils::Logging('report', $Reports);
		
	}
	
	/**
	 * Fetch all valid merchants.
	 * 
	 * @return array
	 */
	protected function _fetchMerchants() {
		$sql_merchants = "SELECT id, email, name, address, city, status
			FROM tbl_merchant_account
			WHERE name IS NOT NULL AND password IS NOT NULL AND status<>99 AND country_code='FI'
			AND email NOT LIKE :email";
		$params = array(':email' => '%@yamii.com');
		$connection = Yii::app()->db;
		$command = $connection->createCommand($sql_merchants);
		$rows = $command->queryAll(true, $params);
		$connection->active = false;
		
		return $rows;
	}
	
	/**
	 * fetch the report data for each merchant.
	 * 
	 * @param array $Merchants
	 * @return array
	 */
	protected function _fetchBizReport($Merchants, $date) {
		//set the first date and last date in the current month
		$firstday = date('Y-m-01', strtotime("$date -1 month"));
		$lastday = date('Y-m-d', strtotime("$firstday +1 month -1 day"));
		
		$Reports = array();
		
		$connection = Yii::app()->db;
		foreach ($Merchants as $k => $v) {
			$item = $v;	//get into item
			$item['firstday'] = $firstday;
			$item['lastday'] = $lastday;
			
			$MerchantId = $v['id'];
			
			//0. $item['followers'] = ?
			$sql_follower = 'SELECT COUNT(*) FROM tbl_user_follow_merchant
				WHERE id_merchant=:id_merchant
				  AND action_time BETWEEN :firstday AND :lastday';
			$command = $connection->createCommand($sql_follower);
			$followers = $command->queryScalar(array(
				':id_merchant' => $MerchantId,
				':firstday' => $firstday,
				':lastday' => $lastday,
			));
			$item['followers'] = (int)$followers;
			
			//1. find all specials
			$sql_specials = 'SELECT id FROM tbl_merchant_special
					WHERE id_merchant=:id_merchant AND status=1 
					AND ( (type=2 AND start_date BETWEEN :firstday AND :lastday)
							OR (type=1 AND (start_date BETWEEN :firstday AND :lastday)
								OR (end_date BETWEEN :firstday AND :lastday)
								OR (start_date<:firstday AND end_date>:lastday) )
						)';
			$command = $connection->createCommand($sql_specials);
			$rows_special = $command->queryAll(true, array(
				':id_merchant' => $MerchantId,
				':firstday' => $firstday,
				':lastday' => $lastday,
			));
			
			$published_num = count($rows_special);
			$item['specials'] = (int)$published_num;
			if ($published_num > 0) {
				$ids_string = '';
				foreach ($rows_special as $r) {
					$ids_string .= $r['id'] . ',';
				}
				$ids_string = substr($ids_string, 0, -1);

				//2. fetch specials with claim count
				$sql_claim = 'SELECT SUM(number)
					FROM tbl_merchant_special_redeem
					WHERE id_special IN ('. $ids_string .')
					AND insert_timestamp BETWEEN :firstday AND :lastday';
				$command = $connection->createCommand($sql_claim);
				$claims = $command->queryScalar(array(
					':firstday' => $firstday,
					':lastday' => $lastday,
				));
				$item['offer_claims'] = (int)$claims;

				//3. fetch specials with price and redeem number
				$sql_redeem = 'SELECT s.price, r.number FROM tbl_merchant_special s
					INNER JOIN tbl_merchant_special_redeem r ON r.id_special=s.id AND r.redeemed=2
					WHERE r.id_special IN ('. $ids_string .')
					AND r.insert_timestamp BETWEEN :firstday AND :lastday';
				$command = $connection->createCommand($sql_redeem);
				$rows_revenue = $command->queryAll(true, array(
					':firstday' => $firstday,
					':lastday' => $lastday,
				));
				foreach ($rows_revenue as $r) {
					$item['offer_redeems'] += $r['number'];
					$item['offer_revenue'] += ($r['price'] * $r['number']);//calculate the revenue
				}

				//4. fetch the view times
				$sql_view = 'SELECT count(*) FROM tbl_merchant_special_viewed WHERE id_special IN ('. $ids_string .')';
				$command = $connection->createCommand($sql_view);
				$views = $command->queryScalar();
				$item['offer_views'] = (int)$views;
			}
			
			$Reports[] = $item;
		}
		$connection->active = false;
		
		return $Reports;
	}
	
	/**
	 * Makes reports with HTML template and generates charts here, save html string into table tbl_merchant_report_monthly.
	 * 
	 * @param array $Reports
	 */
	protected function _makeBizReport($Reports) {
		foreach ($array as $k => $v) {
			if ($v['specials'] > 0) {
				
			}
		}
	}
}